package com.jiudao.dao;

import org.apache.commons.lang.StringUtils;
import org.springframework.stereotype.Repository;

import com.google.gson.JsonArray;
import com.google.gson.JsonObject;
import com.jiudao.entity.Branch;
import com.jiudao.entity.JylRiskconPerson;
import com.jiudao.entity.PersonNewOld;
import com.jiudao.entity.Riskcon;
import com.jiudao.entity.Risklist;
/**
 * 客群详细分析dao
 * @author jpp
 *
 */
@Repository
public class CustomerDetailDao extends BaseDao{
	
	/**
	 * 四大客群详细分析（总）
	 * @param risk_choose 选择的险种
	 * @param startTime 起始时间
	 * @param endTime 终止时间
	 * @param ing 分公司名
	 * @return
	 */
	public JsonObject getall(JsonObject jsonParam){
		String riskChoose= jsonParam.get("risk_choose").getAsString();
		String startTime= jsonParam.get("startDate").getAsString();
		String endTime= jsonParam.get("endDate").getAsString();
		String branchCode= jsonParam.get("branchCode").getAsString();
		String start = "";
		String end = "";
		String intorgCode="";
		String mark="";
		if(!StringUtils.isEmpty(riskChoose))
			mark=" and "+riskcon.column(Riskcon.CLASSCODE)+"='"+riskChoose+"'";
		if(!StringUtils.isEmpty(startTime))
			start = " and "+riskcon.column(Riskcon.APPDATE)+" >= '"+startTime+"'";
		if(!StringUtils.isEmpty(endTime))
			end = " and "+riskcon.column(Riskcon.APPDATE)+" <= '"+endTime+"'";
		if(!StringUtils.isEmpty(branchCode))
			intorgCode = " and "+riskcon.column(Riskcon.BRANCH)+"='"+branchCode+"'";
		//四大客群分布
		String sql_fb="select count(1) as num,"+jylRiskconPerson.column(JylRiskconPerson.PERSON_MARK)+" as person_mark from "+riskcon.tablename()
//				+" inner join "+risklist.tablename()+" on "+riskcon.column(Riskcon.CLASSCODE)+"="+risklist.column(Risklist.CLASSCODE)
				+" inner join "+jylRiskconPerson.tablename()+" on "+riskcon.column(Riskcon.POLICYNO)+"="+jylRiskconPerson.column(JylRiskconPerson.POLICYNO)
				+" where "+riskcon.column(Riskcon.APPF)+"='1'"
				+intorgCode
				+start
				+end
				+mark
				+" group by "+jylRiskconPerson.column(JylRiskconPerson.PERSON_MARK);
		logger.info(sql_fb);
		String columns_fb="num,person_mark";
		//长短险件数
		String sql_long="select count(1) as num,"+jylRiskconPerson.column(JylRiskconPerson.LONG_RISK)+" as risk_mark from "+riskcon.tablename()
//				+" inner join "+risklist.tablename()+" on "+riskcon.column(Riskcon.CLASSCODE)+"="+risklist.column(Risklist.CLASSCODE)
				+" inner join "+jylRiskconPerson.tablename()+" on "+riskcon.column(Riskcon.POLICYNO)+"="+jylRiskconPerson.column(JylRiskconPerson.POLICYNO)
				+" where "+riskcon.column(Riskcon.APPF)+"='1'"
				+intorgCode
				+start
				+end
				+mark
				+" group by "+jylRiskconPerson.column(JylRiskconPerson.LONG_RISK)+";";
		logger.info(sql_long);
		String columns_long="num,risk_mark";
		//投保人数
//		String sql_apid="select count(1) as num from (select distinct "+riskcon.column(Riskcon.APID)+" from "+riskcon.tablename()
//				+" inner join "+risklist.tablename()+" on "+riskcon.column(Riskcon.CLASSCODE)+"="+risklist.column(Risklist.CLASSCODE)
//				+" where "+riskcon.column(Riskcon.APPF)+"='1'"
//				+intorgCode
//				+start
//				+end
//				+mark
//				+" );";
		
		String sql_apid_ = "select "
			   +" count(distinct apid) as num "
			   +" from  "
			    +" jylbase.riskcon as riskcon  "
//			+" inner join jylfx.jyl_risklist as jyl_risklist on riskcon.classcode=jyl_risklist.classcode "
			+" where "
			   +"  riskcon.appf='1'"
			   +start
			   +end
			   +mark
			   +intorgCode;
		logger.info(sql_apid_);
		String columns_apid="num";
		//被投保人数
//		String sql_pid="select count(1) as num from (select distinct "+riskcon.column(Riskcon.PID)+" from "+riskcon.tablename()
//				+" inner join "+risklist.tablename()+" on "+riskcon.column(Riskcon.CLASSCODE)+"="+risklist.column(Risklist.CLASSCODE)
//				+" where "+riskcon.column(Riskcon.APPF)+"='1'"
//				+intorgCode
//				+start
//				+end
//				+mark
//		+" );";
		
		String sql_pid_ = "select "
		   +" count(distinct RISKCON.PID) as num "
			+" from "
			    +" JYLBASE.RISKCON "
//			+" inner join JYLFX.JYL_RISKLIST on RISKCON.CLASSCODE=JYL_RISKLIST.CLASSCODE "
			+" where "
		    +" RISKCON.APPF='1' "
		    +start
			+end
			+mark
		    +intorgCode;
		logger.info(sql_pid_);
		String columns_pid="num";
		//老客户数
		String sql_old="SELECT count(1) as num from (select distinct "+riskcon.column(Riskcon.APID)+" as id from "+riskcon.tablename()
//				+" inner join "+risklist.tablename()+" on "+riskcon.column(Riskcon.CLASSCODE)+"="+risklist.column(Risklist.CLASSCODE)
				+" inner join "+jylRiskconPerson.tablename()+" on "+riskcon.column(Riskcon.POLICYNO)+"="+jylRiskconPerson.column(JylRiskconPerson.POLICYNO)
				+" inner join "+personNewOld.tablename()+" on "+riskcon.column(Riskcon.APID)+"="+personNewOld.column(PersonNewOld.ID)
				+" where 1=1 "
				+" and "+jylRiskconPerson.column(JylRiskconPerson.LONG_RISK)+"='长险'  "
				+" and  "+riskcon.column(Riskcon.APPF)+"='1'"
				+intorgCode
				+start
				+end
				+mark
				+" and "+personNewOld.column(PersonNewOld.CUSTMK)+"='新客户') as a;";
		logger.info(sql_old);
		String columns_old="num";
		//男女人数分布
		String sql_sex="SELECT count(1) as num,a.sex as sex FROM "
				+" (select distinct "+riskcon.column(Riskcon.APID)+" as apid,"+personNewOld.column(PersonNewOld.SEX)+" as sex from "+riskcon.tablename()
//				+" inner join "+risklist.tablename()+" on "+riskcon.column(Riskcon.CLASSCODE)+"="+risklist.column(Risklist.CLASSCODE)
				+" inner join "+personNewOld.tablename()+" on "+riskcon.column(Riskcon.APID)+"="+personNewOld.column(PersonNewOld.ID)
				+" where "+riskcon.column(Riskcon.APPF)+"='1'"
					+intorgCode
					+start
					+end
					+mark
				+" ) as a"
				+" group by a.sex;";
		logger.info(sql_sex);
		String columns_sex="num,sex";
		JsonObject json =new JsonObject();
		json.add("cusDetail", executeQuery(sql_fb, columns_fb));//四大客群分布
//		JsonArray pidNum = executeQuery(sql_apid_, columns_apid);
		json.addProperty("apidNum", executeQuery(sql_apid_, columns_apid).get(0).getAsJsonObject().get("num").getAsInt());//投保人
		json.addProperty("pidNum", executeQuery(sql_pid_, columns_pid).get(0).getAsJsonObject().get("num").getAsInt());//被投保人
		json.add("riskMark", executeQuery(sql_long, columns_long));//长短险分布
		json.addProperty("oldNum", executeQuery(sql_old, columns_old).get(0).getAsJsonObject().get("num").getAsInt());//老客户数
		json.add("sexNum", executeQuery(sql_sex, columns_sex));//性别分布
		return json;
	}
	
	/**
	 * 全部的险种
	 * @return
	 */
	public JsonArray getRiskList(){
		String sql="select classname,classcode from jyl_risklist group by classname,classcode;";
		logger.info(sql);
		String columns="classname,classcode";
		return executeQuery(sql, columns);
	}
	/**
	 * 全部的分公司
	 * @return
	 */
	public JsonArray getIntorgInfo(){
		String sql="select intorgname,branch from branch;";
		logger.info(sql);
		String columns="intorgname,branch";
		return executeQuery(sql, columns);
	}
	
}
